/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import com.mysql.jdbc.exceptions.DeadlockTimeoutRollbackMarker;
import modelo.Lectura;
import dao.generic.GenericDAO;
import java.util.Date;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.persistence.OrderBy;
import javax.swing.JOptionPane;
import modelo.Grupo;
import modelo.GrupoLectura;
import modelo.Historial;
import modelo.Sustrato;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.type.StringType;
import org.hibernate.type.Type;

/**
 *
 * @author Administrador
 */
public class LecturaDAO extends GenericDAO {

  public Lectura getLecturabyId(int idSensorRegistro) {
    Lectura sr = null;
    try {
      iniciarOperacion();
      sr = (Lectura) sesion.get(Lectura.class, idSensorRegistro);

    } finally {
      sesion.close();
    }
    return sr;
  }

  public int ultimoRegistro() {
    Integer ultimo;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("SELECT MAX(id) FROM Lectura");
      ultimo = (Integer) query.uniqueResult();
      if (ultimo == null) {
        ultimo = 0;
      }
      System.out.println("El ultimo fue: " + ultimo);

    } finally {
      sesion.close();
    }
    return ultimo;
  }

  public List lecturaDia(Date fecha) {
    List lista = null;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("SELECT elements(h.lectura) FROM Historial as h where h.fecha= :fecha");
      query.setParameter("fecha", fecha);
      lista = query.list();
    } catch (HibernateException e) {
      System.out.println("A ocurrido un error " + e);
    }
    return lista;
  }

  public void cerrarSesionBeforeIterator() {
    if (sesion.isOpen()) {
      System.out.println("Se encuentra abierta la sesion");
      sesion.close();
    } else {
      System.out.println("No hay sesion abierta");
    }
  }

  public List LecturaDiaCriteria(Date fecha, String[] sustratos) {
    List lista = null;
    try {

      iniciarOperacion();
      DetachedCriteria historial = DetachedCriteria.forClass(Historial.class).add(Restrictions.eq("fecha", fecha)).setProjection(Property.forName("id"));
      DetachedCriteria sustrato = DetachedCriteria.forClass(Sustrato.class).add(Restrictions.in("nombre", sustratos)).setProjection(Property.forName("id"));

      Criteria criteria = sesion.createCriteria(Lectura.class, "l");
      criteria.createCriteria("l.historial", "h");
      criteria.createCriteria("l.sustrato", "s");
      criteria.add(Property.forName("h.id").in(historial));
      criteria.add(Property.forName("s.id").in(sustrato));
      criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

      lista = criteria.list();

      for (Object obj : lista) {
        Lectura l = (Lectura) obj;
        System.out.println(l.getId());
      }

    } catch (HibernateException e) {
      System.out.println("Ha ocurrido un error" + e);
    }
    return lista;
  }

  public Integer[] consultaUltHora(Date fecha) {
    Integer[] matriz = null;
    ArrayList<Integer> lista = new ArrayList<Integer>();
    Iterator i;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("select MAX(l.id), l.sustrato.id from Lectura as l where l.sustrato.id in (select id from Sustrato)"
              + " and l.historial in (from Historial where fecha= :fecha)  group by l.sustrato.id");
      query.setParameter("fecha", fecha);
      i = query.iterate();
      while (i.hasNext()) {
        Object[] obj = (Object[]) i.next();
        int id = (Integer) obj[0];
        lista.add(id);
        System.out.println(obj[0] + "--" + obj[1]);
      }
      matriz = (Integer[]) lista.toArray(new Integer[0]);
    } finally {
      sesion.close();
    }
    return matriz;
  }

  public List consultaEstadoValvula(Integer[] ids) {
    List lista = null;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Lectura.class);
      criteria.add(Restrictions.in("id", ids));
      lista = criteria.list();
      for (Object l : lista) {
        Lectura lec = (Lectura) l;
        System.out.println(lec.getId());
        System.out.println(lec.getSustrato().getId());
      }
    } catch (HibernateException e) {
      System.out.println("A ocurrido un error " + e);
    }
    return lista;
  }
  //Prueba de criteria**********************************************

  public void pruebaCriteria() {
    List lista;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Sustrato.class, "s");

//      criteria.createCriteria("grupo").setProjection(Projections.rowCount());
      criteria.createCriteria("grupo");
//      criteria.setFetchMode("Grupo", FetchMode.JOIN).setProjection(null);
      ProjectionList projList = Projections.projectionList();



      projList.add(Projections.groupProperty("s.id"));
      projList.add(Projections.property("nombre"));
//       projList.add(Projections.groupProperty("s.nombre"));

      projList.add(Projections.count("grupo"));
//     
      criteria.setProjection(projList);

      lista = criteria.list();
      System.out.println(lista);
      Iterator i = lista.iterator();

      if (!i.hasNext()) {
        System.out.println("No hay datos cabron");
      } else {
        while (i.hasNext()) {
          Object[] obj = (Object[]) i.next();
          for (int x = 0; x < obj.length; x++) {
            System.out.println(obj[x]);
          }
          System.out.println("-----------------");
        }
      }
    } finally {
      sesion.close();
    }

  }

  public Integer[] consultaUltHoraCriteria(Date fecha, String[] sustratos) {
    Integer[] matriz = null;
    ArrayList<Integer> lista = new ArrayList<Integer>();
    Iterator i;
    try {
      iniciarOperacion();
      DetachedCriteria idSustrato = DetachedCriteria.forClass(Sustrato.class).add(Restrictions.in("nombre", sustratos)).setProjection(Property.forName("id"));

      DetachedCriteria idHistorial = DetachedCriteria.forClass(Historial.class).add(Restrictions.eq("fecha", fecha)).setProjection(Property.forName("id"));

      Criteria criteria = sesion.createCriteria(Lectura.class, "l");
      criteria.createCriteria("sustrato", "s");
      criteria.createCriteria("historial", "h");
      ProjectionList listProy = Projections.projectionList();
      listProy.add(Projections.max("l.id"));
      listProy.add(Projections.groupProperty("s.id"));

      criteria.add(Property.forName("s.id").in(idSustrato));
      criteria.add(Property.forName("h.id").in(idHistorial));


      criteria.setProjection(listProy);
      i = criteria.list().iterator();

      while (i.hasNext()) {
        Object[] obj = (Object[]) i.next();
        int id = (Integer) obj[0];
        lista.add(id);
        System.out.println(obj[0] + "--" + obj[1]);
      }
      matriz = (Integer[]) lista.toArray(new Integer[0]);
    } finally {
      sesion.close();
    }
    return matriz;
  }

  public List consultaLecturasHistoricos(String nombreSustrato, Date[] fecha, String[] sensores) {
    List i = null;
    try {
      iniciarOperacion();

      DetachedCriteria historial = DetachedCriteria.forClass(Historial.class);
      if (fecha.length == 1) {
        historial.add(Restrictions.eq("fecha", fecha[0]));
      }
      if (fecha.length == 2) {
        historial.add(Restrictions.between("fecha", fecha[0], fecha[1]));
      }
      historial.setProjection(Projections.property("id"));

      DetachedCriteria sustrato = DetachedCriteria.forClass(Sustrato.class).add(Restrictions.eq("nombre", nombreSustrato)).setProjection(Projections.property("id"));

      Criteria criteria = sesion.createCriteria(Lectura.class, "l");
      criteria.createCriteria("sustrato", "s");
      criteria.createCriteria("historial", "h");
      ProjectionList proyeccion = Projections.projectionList();
      proyeccion.add(Projections.property("hora"));
      for (int x = 0; x < sensores.length; x++) {
        if (sensores[x] != null) {
          proyeccion.add(Projections.property(sensores[x].toLowerCase()));
        }
      }
      criteria.add(Property.forName("h.id").in(historial));
      criteria.add(Property.forName("s.id").in(sustrato));

      criteria.setProjection(proyeccion);

      i = criteria.list();


    } catch (HibernateException e) {
      System.out.println("Ha ocurrido un error en la consulta" + e);
    }
    return i;
  }

  public void lecturasByDia() {
    Iterator i;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Lectura.class, "lec").setProjection(Projections.sum("a"));

    } finally {
      sesion.close();
    }

  }

  //realizando una prueba para obtener solo la hora de una lectura, sin minutos ni segundos
  public void prubaHora() {
    try {
      iniciarOperacion();


      Criteria criteria = sesion.createCriteria(Lectura.class);
      ProjectionList proj = Projections.projectionList();
      proj.add(Projections.avg("a"));
      proj.add(Projections.avg("c"));
      proj.add(Projections.avg("d"));
      proj.add((Projections.sqlGroupProjection(
              "hour(hora) as hora",
              "hour(hora)",
              new String[]{"hora"},
              (org.hibernate.type.Type[]) new Type[]{Hibernate.INTEGER})));


      criteria.setProjection(proj);
//      criteria.addOrder(Order.asc("hor"));
//      criteria.setResultTransformer(CriteriaSpecification.PROJECTION);
//      List lista = criteria.list();
      Iterator i = criteria.list().iterator();


//      for(Object ob:lista){
//        System.out.println(ob.toString() );
//        //System.out.println( new Integer(ob.toString()) );
//      }
      int con = 1;

      while (i.hasNext()) {
        Object[] obj = (Object[]) i.next();
        if (con == 1) {
        }
        for (int x = 0; x < obj.length; x++) {
          System.out.print(con + " " + obj[x]);
        }

        System.out.println("\n");
        con++;
      }

    } finally {
      sesion.close();
    }

  }

  public List consultaLecturaSensores(String nombreSustrato, Date[] fecha, String sensor) {
    List i = null;
    try {
      iniciarOperacion();

      DetachedCriteria historial = DetachedCriteria.forClass(Historial.class);
      if (fecha.length == 1) {
        historial.add(Restrictions.eq("fecha", fecha[0]));
      }
      if (fecha.length == 2) {
        historial.add(Restrictions.between("fecha", fecha[0], fecha[1]));
      }
      historial.setProjection(Projections.property("id"));

      DetachedCriteria sustrato = DetachedCriteria.forClass(Sustrato.class).add(Restrictions.eq("nombre", nombreSustrato)).setProjection(Projections.property("id"));


      Criteria criteria = sesion.createCriteria(Lectura.class, "l");
      criteria.createCriteria("sustrato", "s");
      criteria.createCriteria("historial", "h");

      ProjectionList proyeccion = Projections.projectionList();

      proyeccion.add(Projections.avg(sensor));
      proyeccion.add((Projections.sqlGroupProjection(
              "hour(hora) as hora",
              "hour(hora)",
              new String[]{"hora"},
              (org.hibernate.type.Type[]) new Type[]{Hibernate.INTEGER})));
      proyeccion.add(Projections.groupProperty("h.fecha"), "fe");

      criteria.add(Property.forName("h.id").in(historial));
      criteria.add(Property.forName("s.id").in(sustrato));

      criteria.addOrder(Order.asc("fe"));
      criteria.setProjection(proyeccion);

      i = criteria.list();


    } catch (HibernateException e) {
      System.out.println("Ha ocurrido un error en la consulta" + e);
    }
    return i;
  }
  
  public List consultaGastoAcumuladoByDia(String nombreSustrato, Date date) {
    List lista;
    try {
      iniciarOperacion();
      SimpleDateFormat formatoFecha = new SimpleDateFormat("yyyy-MM-dd");
      String fecha = formatoFecha.format(date);
      
      Query query2 = sesion.createSQLQuery(" select SUM(con1.acumulado) as acumuladoTotal, con1.hora"
              + " from (select hour(l.hora) as hora,SUM(gl.estadoValvula) *g.gasto as acumulado"
              + " from lectura as l, grupo_lectura as gl, grupo as g, historial h"
              + " where h.id in"
              + " (select id from historial where fecha='"+fecha+"')"
              + " and l.historial_id=h.id "
              + " and sustrato_id in"
              + " (select id from sustrato where nombre='"+nombreSustrato+"')"
              + " and gl.lectura_id=l.id"
              + " and g.id = gl.grupo_id"
              + " and g.grupo in"
              + " (select grupo from grupo where sustrato_id in (select id from sustrato where nombre='"+nombreSustrato+"') )"
              + " group by hour(l.hora),g.grupo)  as con1"
              + " group by con1.hora order by con1.hora");

      lista = query2.list();

    } finally {
      sesion.close();
    }
    return lista;
  }

  public List consultaGastoAcumuladoByPeriodo(String nombreSustrato, Date[] date) {
    List lista;
    try {
      iniciarOperacion();
      SimpleDateFormat formatoFecha = new SimpleDateFormat("yyyy-MM-dd");

      String fechaIni = formatoFecha.format(date[0]);
      String fechaFin = formatoFecha.format(date[1]);
      Query query2 = sesion.createSQLQuery(" select con1.fecha,SUM(con1.acumulado)"
              + " from (select h.fecha as fecha,SUM(gl.estadoValvula) *g.gasto as acumulado "
              + " from lectura as l, grupo_lectura as gl, grupo as g, historial as h"
              + " where h.id in"
              + " (select id from historial where fecha between '"+fechaIni+"' and '"+fechaFin+"')"
              + " and l.historial_id=h.id"
              + " and sustrato_id in"
              + " (select id from sustrato where nombre='"+nombreSustrato+"')"
              + " and gl.lectura_id=l.id and g.id = gl.grupo_id"
              + " and g.grupo in"
              + " (select grupo from grupo where sustrato_id in"
              + " (select id from sustrato where nombre='"+nombreSustrato+"'))"
              + " group by g.grupo,h.fecha) as con1"
              + " group by con1.fecha");

      lista = query2.list();

    } finally {
      sesion.close();
    }
    return lista;
  }
}
